﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DevExpress.XtraEditors;
using System.Windows.Forms;
namespace UI
{

    public static class Comm
    {
        
        public static void ShowErorr(string msg)
        {
            XtraMessageBox.Show(msg, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

        public static void ShowInfo(string msg)
        {
            XtraMessageBox.Show(msg, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        public static void ShowAlert(string msg)
        {
            XtraMessageBox.Show(msg, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

        public static DialogResult ShowDialog(string msg)
        {
            return XtraMessageBox.Show(msg, "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button2);
        }



        ///<summary>///读取xls\xlsx格式的Excel文件的方法 
        ///</ummary>///<param name="path">待读取Excel的全路径</param>
        ///<returns></returns>
        public static DataTable ReadExcelToTable(string path, string SheetName,bool firstisdata)
        {
            string hdr = "";
            //连接字符串
            if (firstisdata)
            {
                 hdr = "yes";
            }
            else
            {
                hdr = "no";
            }
            string connstring =string.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1';",path,hdr);
            // Office 07及以上版本 不能出现多余的空格 而且分号注意
            //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  
            //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串  可根据自己的情况选择 或者程序判断要用哪一个连接字符串
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                
                //DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                ////得到所有sheet的名字    
                //string firstSheetName = sheetsName.Rows[0][2].ToString();
                //得到第一个sheet的名字    
                string sql = string.Format("SELECT * FROM [{0}]", SheetName);
                //查询字符串     
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);
                return set.Tables[0];
            }
        }

        /// <summary>
        /// 取excel工作簿
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="firstisdata">首行是列名</param>
        /// <returns></returns>
        public static DataTable getSheets(string path, bool firstisdata)
        {
            try
            {
                //连接字符串
                string hdr = "";
                //连接字符串
                if (firstisdata)
                {
                    hdr = "yes";
                }
                else
                {
                    hdr = "no";
                }
                string connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1';", path, hdr);
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    return conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });


                }
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }

        public static DataColumnCollection getSheetsCloum(string path,string SheetName,bool firstisdata)
        {
            //连接字符串
            string hdr = "";
            //连接字符串
            if (firstisdata)
            {
                hdr = "yes";
            }
            else
            {
                hdr = "no";
            }
            string connstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1';", path, hdr);
            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();

                //DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                ////得到所有sheet的名字    
                //string firstSheetName = sheetsName.Rows[0][2].ToString();
                //得到第一个sheet的名字    
                string sql = string.Format("SELECT * FROM [{0}]", SheetName);
                //查询字符串     
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);
                return set.Tables[0].Columns;
            }
        }

        public static void openForm(string formClass)
        {

            if (my.mainform.MdiChildren.Count(p => p.GetType().ToString() == formClass) ==1)
            {
                my.mainform.MdiChildren[0].Show();
                my.mainform.MdiChildren.Single(p => p.GetType().ToString() == formClass).Activate();
            }
            else
            {
                Type t1 = Type.GetType(formClass);

                XtraForm frm1 = (XtraForm)Activator.CreateInstance(t1);
                frm1.MdiParent = my.mainform;
                frm1.Show();
            }
        }

        public static void openDialogForm(string formclass,object parm)
        {
            Type t1 = Type.GetType(formclass);
            if (parm==null)
            {
                XtraForm frm1 = (XtraForm)Activator.CreateInstance(t1);
                frm1.ShowDialog();
            }
            else
            {
                XtraForm frm1 = (XtraForm)Activator.CreateInstance(t1, parm);
                frm1.ShowDialog();
            }
            

            
        }

        
    }

     
}
